package com.jn.util;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.jsp.JspWriter;
import javax.servlet.jsp.PageContext;
import javax.swing.JWindow;

import org.apache.jasper.runtime.JspWriterImpl;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ExcelUtils {

	/**
	 * @Title export
	 * @Description 导出
	 * @param request
	 * @param response
	 * @param filePath
	 * @param fileName
	 * @param encoding
	 * @param decoding
	 * @param sheetName
	 * @param titles
	 * @param data
	 * @throws Exception
	 */
	public static void export(HttpServletRequest request,
			HttpServletResponse response, String filePath, String fileName,
			String encoding, String decoding, String sheetName,
			String[] titles, List<Object[]> data) throws Exception {
		createExcelFile(filePath, fileName, sheetName, titles, data);
		download(request, response, filePath, fileName, encoding, decoding);
	}

	/**
	 * @Title createExcelFile
	 * @Description 创建excel文件
	 * @param filePath
	 * @param fileName
	 * @param sheetName
	 * @param titles
	 * @param data
	 * @return
	 * @throws Exception
	 */
	public static File createExcelFile(String filePath, String fileName,
			String sheetName, String[] titles, List<Object[]> data)
			throws Exception {
		WritableWorkbook workbook = null;
		File excel = createEmptyFile(filePath, fileName);
		workbook = Workbook.createWorkbook(excel);
		createSheet(sheetName, titles, data, workbook, 0);
		workbook.write();
		workbook.close();
		return excel;
	}

	/**
	 * @Title download
	 * @Description 下载
	 * @param request
	 * @param response
	 * @param filePath
	 * @param fileName
	 * @param encoding
	 * @param decoding
	 * @return
	 * @throws Exception
	 */
	public static String download(HttpServletRequest request,
			HttpServletResponse response, String filePath, String fileName,
			String encoding, String decoding) throws Exception {
		String file = filePath + File.separator + fileName;
		File f = new File(file);
		if (!f.exists()) {
			return null;
		}
		FileInputStream fis = new FileInputStream(f);
		BufferedInputStream in = new BufferedInputStream(fis);
		byte[] buf = new byte[1024];
		int len = 0;

		response.reset(); // 非常重要
		int dotIdx = -1;
		if ((dotIdx = fileName.lastIndexOf(".")) > 0) {
			String subfixName = fileName.substring(dotIdx + 1);
			if (subfixName.equalsIgnoreCase("xls")) {
				response.setContentType("application/vnd.ms-excel");
			} else if (subfixName.equalsIgnoreCase("apk")) {
				response.setContentType("application/vnd.android.package-archive");
			} else {
				response.setContentType("application/x-msdownload");
			}
		}
		if (request.getHeader("user-agent").indexOf("MSIE") != -1) {
			fileName = URLEncoder.encode(fileName, encoding);
		} else {
			fileName = new String(fileName.getBytes(encoding), decoding);
		}
		response.setHeader("Content-Disposition", "attachment; filename="
				+ fileName);
		OutputStream out = response.getOutputStream();
		try {
			while ((len = in.read(buf)) > 0) {
				out.write(buf, 0, len);
			}
			out.flush();
		} catch (Exception e) {
			e.printStackTrace();
		}
		in.close();
		out.close();
		fis.close();
		return "sucess";
	}

	/**
	 * @Title createEmptyFile
	 * @Description 创建空文件
	 * @param path
	 * @param fileName
	 * @return
	 */
	public static File createEmptyFile(String path, String fileName) {
		File file = new File(path + "/" + fileName);
		if (file.exists()) {
			file.delete();
		} else if (path != null) {
			file = new File(path);
			if (!file.exists())
				file.mkdirs();
			file = new File(path + "/" + fileName);
		} else {
			file = new File(fileName);
		}

		return file;
	}

	/**
	 * @Title createSheet
	 * @Description 创建sheet页
	 * @param sheetName
	 * @param titles
	 * @param data
	 * @param workbook
	 * @param sheetId
	 * @throws WriteException
	 * @throws RowsExceededException
	 * @throws IOException
	 */
	public static void createSheet(String sheetName, String[] titles,
			List<Object[]> data, WritableWorkbook workbook, int sheetId)
			throws WriteException, RowsExceededException, IOException {
		WritableSheet sheet = workbook.createSheet(sheetName, sheetId);

		WritableFont font = new WritableFont(WritableFont.TIMES, 13,
				WritableFont.BOLD, false);
		WritableCellFormat format = new WritableCellFormat(font);
		// 设置输出格式为纯文本
		WritableCellFormat textFormat = new WritableCellFormat(
				NumberFormats.TEXT);
		textFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 单元格内容垂直居中
		// 标题
		for (int i = 0; i < titles.length; i++) {
			sheet.addCell(new Label(i, 0, titles[i], format));
		}

		// 内容
		Label cell = null;
		for (int i = 0; i < data.size(); i++) {
			Object[] o = data.get(i);
			for (int j = 0; j < o.length; j++) {
				Object obj = o[j];
				if (obj != null) {
					cell = new Label(j, (i + 1), String.valueOf(obj),
							textFormat);
					sheet.addCell(cell);
				}
			}
		}

	}

}
